<?php

class StaffController extends AppController
{
    # Controller Name to be used
    var $name = 'Staff';

    # Models to be used
    var $uses = array(
        'StaffUser', 'StudentUser', 'FeeType',
        'Transaction', 'TransactionItem', 'Fee',
        'Program', 'StudentServiceRequest', 'BankBranch',
        'AcademicSession','ServiceCharge'
    );

    # set the layout to use
    var $layout = 'staff_layout';

    function __validateLoginStatus()
    {
        if ($this->params['action'] != 'login' && $this->params['action'] != 'logout') {
            if ($this->Session->check('loginData') == false) {
                $this->redirect(array('action' => 'login?ref=' . $this->params['action']));
            } else {
                $auth_user = $this->Session->read('loginData');
                //check if the user has not done multiple login
                $db_login_session = $this->getLoginSession($auth_user['id']);
                $login_session = $auth_user['login_session'];
                if ($db_login_session != $login_session) {
                    $this->Session->setFlash('Your session has expired!');
                    $this->redirect(array('action' => 'logout'));
                }
                //authorize the user, if the user is not authorized to view this page then redirect them to their session
                //$auth_user = $this->Session->read('loginData');
                if ($auth_user['user_type'] != 'staff_user') {
                    if ($auth_user['user_type'] == 'student_user') {
                        $this->redirect(array('controller' => 'Student', 'action' => 'index'));
                    } elseif ($auth_user['user_type'] == 'bank_user') {
                        $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
                    } else {
                        $this->redirect(array('action' => 'logout'));
                    }
                } else { // For non Admin Staff only
                    $user_level = $auth_user['user_level'];
                    if ($user_level == 'Admin') {
                        $this->redirect(array('controller' => 'StaffAdmin', 'action' => 'index'));
                    }
                }
            }
        }
    }

    private function getLoginSession($user_id)
    {
        $data = $this->StaffUser->find('first', array(
            'conditions' => array('id' => $user_id),
            'recursive' => -1
        ));
        return $data['StaffUser']['login_session'];
    }

    function  beforeFilter()
    {
        parent::beforeFilter();
        $this->__validateLoginStatus();
    }


    public function login()
    {
        if ($this->request->is('post')) {
            $logme  = $this->request->data['StaffUser'];
            $logme = $this->StaffUser->sanitize($logme);
            $result = $this->StaffUser->login($logme['username'], $logme['password']);
            //exit;
            if ($result['status']) {
                //start a new session for this user
                $loginData = $this->createLoginSession($result['data']['StaffUser']);
                //write data to session
                $this->Session->write('loginData', $loginData);

                $this->redirect(array('action' => 'index'));
            } else {
                $this->Session->setFlash($result['msg']);
            }
        }

        $this->layout = 'login_layout';
    }

    private function createLoginSession($data)
    {
        $login_session = $this->randomString(50);
        $data['login_session'] = $login_session;
        $this->StaffUser->save(array(
            'id' => $data['id'],
            'login_session' => $login_session
        ));
        return $data;
    }

    public function logout()
    {
        $this->Session->delete('loginData');
        $this->Session->destroy();
        $this->redirect(array('action' => 'login'));
    }


    # Controller actions
    /**
     * @name index
     * @param void
     * @return Array of data.
     * @access public
     */
    function index()
    {

        //$this->set(compact('page_title'));
    }


    function editprofile()
    {
        if ($this->request->is('post')) {
            $error = false;
            $auth_user = $this->Session->read('loginData');

            //Check if the student is trying to use the same pass as the date of birth , then reject
            /*if(trim($this->request->data['StaffUser']['password']) == $auth_user['date_of_birth']){
                $error = true;
                $this->Session->setFlash("Password Error, You can't use your default password as a new password. ");
            }*/

            if ($this->request->data['StaffUser']['password']) {
                //echo 'yes';
                $this->request->data['StaffUser']['password'] = md5(trim($this->request->data['StaffUser']['password']));
                /*if($auth_user['first_time_pass_changed'] == 'No'){
                    $this->request->data['StaffUser']['first_time_pass_changed'] = 'Yes';
                }*/
            } else {
                $auth_user = $this->Session->read('loginData');
                $this->request->data['StaffUser']['password'] = $auth_user['password'];
            }
            /*if($this->request->data['StaffUser']['cellphone']){
                $cell = $this->request->data['StudentUser']['cellphone'];
                $this->request->data['StudentUser']['cellphone'] = preg_replace('/-/', '', $cell);
            }*/

            if ($error) {
                $this->Session->write('process_error', 'yes');
            } else {
                $result = $this->StaffUser->save($this->request->data);

                if ($result) {
                    //write data to session
                    $user = $this->StaffUser->getUserById($this->request->data['StaffUser']['id']);
                    $this->Session->write('loginData', $user['StaffUser']);
                    $this->Session->setFlash('Your profile info has been updated !');
                    $this->Session->write('process_error', 'no');
                    //$msg ="Dear ".$auth_user['fname']." your account profile has been updated.";
                    //$msg = 'Your have successfully paid GHS'.$total_paying.'. as fees for '.$this->current_academic_year.' academic year.';

                    /* $fon_number = $this->request->data['StudentUser']['cellphone'];
                     $this->Message->sendSMS($msg, $fon_number);*/
                } else {
                    $this->Session->setFlash('Sorry, your profile info could not be updated');
                    $this->Session->write('process_error', 'yes');
                }
            }

            $this->redirect(array('action' => 'editprofile'));
        }
        $controller = $this;
        $this->set(compact('controller'));
    }


    function fees($type = 'get')
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] != 'Accountant') { //For Accountants Only
            $this->redirect(array('controller' => 'Staff', 'action' => 'index'));
        }

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** @var $filter */
                    $filter_program = isset($_POST['filter_program']) ? $_POST['filter_program'] : 0;
                    /** @var $filter */
                    $filter_level = isset($_POST['filter_level']) ? $_POST['filter_level'] : 0;
                    /** @var $filter */
                    $filter_academic_session = isset($_POST['filter_academic_session']) ? $_POST['filter_academic_session'] : 0;

                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                   // $acc_ses_data = $this->AcademicSession->getAcademicSessionById($filter_academic_session);
                    $acca_year = $filter_academic_session;
                    if ($filter_academic_session == 0) {
                        $acca_year = $this->current_academic_year;
                    }

                    //get users id for this company only
                    $condition_array = array(
                        'StudentUser.deleted' => 'n'
                    );

                    if ($filter_program != 0) {
                        $condition_array['StudentUser.program_id'] = $filter_program;
                    }
                    if ($filter_level != 0) {
                        $condition_array['StudentUser.level'] = $filter_level;
                    }

                    if (!empty($search_query)) {
                        if ($qtype == 'username') {
                            $condition_array = array(
                                'StudentUser.index_number' => $search_query,
                                'StudentUser.deleted' => 'n'
                            );
                        } elseif ($qtype == 'name') {
                            $condition_array = array(
                                'OR' => array("StudentUser.fname LIKE" => $search_query . '%', "StudentUser.mname LIKE" => $search_query . '%', "StudentUser.lname LIKE" => $search_query . '%'),
                                'StudentUser.deleted' => 'n'
                            );
                        } else {
                            $condition_array = array(
                                "StudentUser.$qtype LIKE" => $search_query . '%',
                                'StudentUser.deleted' => 'n'
                            );
                        }
                    }
                    $contain = array(
                        'Program' => array('fields' => array('Program.id', 'Program.progid', 'Program.name', 'Program.short_name')),
                    );
                    $fields = array('StudentUser.id', 'StudentUser.index_number', 'StudentUser.fname', 'StudentUser.mname', 'StudentUser.lname', 'StudentUser.level', 'StudentUser.program_id');
                    $data_table = $this->StudentUser->find('all', array('fields' => $fields, 'conditions' => $condition_array, 'contain' => $contain, 'order' => "StudentUser.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->StudentUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {

                            $rt = $this->StudentUser->getRawBills($obj['StudentUser']['index_number'], $acca_year);
                            $fullname = ucwords(strtolower($obj['StudentUser']['fname'] . ' ' . $obj['StudentUser']['mname'] . ' ' . $obj['StudentUser']['lname']));

                            $return_arr[] = array(
                                'id' => $obj['StudentUser']['id'],
                                'cell' => array(
                                    $obj['StudentUser']['id'],
                                    $obj['StudentUser']['index_number'],
                                    $fullname,
                                    $obj['StudentUser']['level'],
                                    $obj['Program']['short_name'],
                                    $this->formatNumber($rt['total_bill'], 'money'),
                                    $this->formatNumber($rt['total_paid'], 'money'),
                                    $this->formatNumber($rt['total_owed'], 'money')
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'load_details':
                    $gdata = $this->StudentUser->find('first', array(
                        'fields' => array('id', 'index_number'),
                        'conditions' => array('StudentUser.id' => $_POST['id']),
                        'recursive' => -1
                    ));
                    $academic_session = $_POST['academic_session'];
                    $acc_ses_data = $this->AcademicSession->getAcademicSessionById($academic_session);
                    $acca_year = $this->current_academic_year;
                    if (!empty($acc_ses_data)) {
                        $acca_year = $acc_ses_data['AcademicSession']['acc_year'];
                    }
                    if ($gdata) {
                        $rt = $this->StudentUser->getRawBills($gdata['StudentUser']['index_number'], $acca_year);
                        $return_arr = array();
                        foreach ($rt['data'] as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['id'],
                                'cell' => array(
                                    $obj['description'],
                                    $this->formatNumber($obj['amount'], 'money'),
                                    $this->formatNumber($obj['paid'], 'money'),
                                    $this->formatNumber($obj['owed'], 'money')
                                )
                            );
                        }
                        return json_encode(array('code' => 0, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('code' => 1, 'rows' => array(), 'mesg' => __('No Record Found')));
                    }

                    break;

                case 'save':
                    $gdata = $this->StudentUser->find('first', array(
                        'fields' => array('id', 'index_number'),
                        'conditions' => array('StudentUser.id' => $_POST['parent_id']),
                        'recursive' => -1
                    ));
                    $data = array('Fee' => $_POST);
                    $data['Fee']['index_number'] = $gdata['StudentUser']['index_number'];
                    $data['Fee']['academic_year'] = $this->current_academic_year;

                    if ($_POST['id'] == 0) {
                        $data['Fee']['created_by'] = $auth_user['id'];
                    } else {
                        $data['Fee']['modified_by'] = $auth_user['id'];
                    }

                    $gdata = $this->Fee->save($data);
                    if ($gdata) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                        } else {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved', 'id' => $this->Fee->id));
                        }
                    } else {
                        return json_encode(array('code' => 1, 'msg' => __('Data cannot be saved')));
                    }

                    break;

                case  'sub_delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('Fee');
                    //;
                    $trans = $this->TransactionItem->find('all', array(
                        'fields' => array('id'),
                        'conditions' => array('fee_id' => $ids),
                        'recursive' => -1
                    ));
                    if ($trans) {
                        echo json_encode(array('code' => 1, 'msg' => "Sorry, can't delete bills that have payment record."));
                        return;
                    }

                    $result = $modObj->deleteAll(array('Fee.id' => $ids), false);
                    /*$result = $modObj->updateAll(
                        array('Fee.deleted' => "'y'",'CustomerAppointment.modified_by' => $auth_user['id']),
                        array('Fee.id' => $ids)
                    );*/
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;

                case 'pre_process_export':
                    /** @var $filter */
                    $filter_program = isset($_POST['filter_program']) ? $_POST['filter_program'] : 0;
                    /** @var $filter */
                    $filter_level = isset($_POST['filter_level']) ? $_POST['filter_level'] : 0;
                    $qtype = isset($_POST['query_column']) ? $_POST['query_column'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';

                    //get users id for this company only
                    $condition_array = array(
                        'StudentUser.deleted' => 'n'
                    );

                    if ($filter_program != 0) {
                        $condition_array['StudentUser.program_id'] = $filter_program;
                    }
                    if ($filter_level != 0) {
                        $condition_array['StudentUser.level'] = $filter_level;
                    }

                    if (!empty($search_query)) {
                        if ($qtype == 'username') {
                            $condition_array = array(
                                'StudentUser.index_number' => $search_query,
                                'StudentUser.deleted' => 'n'
                            );
                        } elseif ($qtype == 'name') {
                            $condition_array = array(
                                'OR' => array("StudentUser.fname LIKE" => $search_query . '%', "StudentUser.mname LIKE" => $search_query . '%', "StudentUser.lname LIKE" => $search_query . '%'),
                                'StudentUser.deleted' => 'n'
                            );
                        } else {
                            $condition_array = array(
                                "StudentUser.$qtype LIKE" => $search_query . '%',
                                'StudentUser.deleted' => 'n'
                            );
                        }
                    }

                    $data_count = $this->StudentUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));
                    if($data_count == 0){
                        echo json_encode(array('code' => 1, 'msg' => 'No Records To Export.'));
                    }
                    else{
                        echo json_encode(array('code' => 0, 'total'=>$data_count ,'msg' => 'Records Found To Export'));
                    }

                    break;
            }
        }
        $fee_type_data = $this->FeeType->find('all', array(
            'fields' => array('id', 'description'),
            'conditions' => array('deleted' => 'n'),
            'recursive' => -1
        ));
        $fee_types = array();
        foreach ($fee_type_data as $dt) {
            $fee_types[] = array(
                'id' => $dt['FeeType']['id'],
                'name' => $dt['FeeType']['description'],
            );
        }

        $program_types = array(array('name' => 'All', 'value' => 0));
        $program_types_data = $this->Program->find('all', array(
            'fields' => array('Program.id', 'Program.name', 'Program.short_name'),
            'order' => array('Program.short_name' => 'asc'),
            'recursive' => -1
        ));
        foreach ($program_types_data as $dt) {
            $program_types[] = array(
                'name' => $dt['Program']['short_name'],
                'value' => $dt['Program']['id']
            );
        }
        $levels = array(array('name' => 'All', 'value' => 0));
        foreach ($this->levels as $dt) {
            $levels[] = array(
                'name' => $dt,
                'value' => $dt
            );
        }

        $current_aca_ses = $this->current_academic_session_id;
        $acc_years = $this->getAcademicYears();
        $academic_sessions = array();
        foreach ($acc_years as $k => $dt) {
            $academic_sessions[] = array(
                'name' => $dt,
                'value' => $k
            );
        }

        $this->set(compact('fee_types', 'levels', 'program_types', 'academic_sessions', 'current_aca_ses'));
    }

    function check_if_export_bill_is_done(){
        $this->autoLayout = false;
        $this->autoRender = false;
        $ses = $this->Session->read('Downloading');
        if($ses){
            echo json_encode(array('code' => 1, 'msg' => 'Not Done Yet'));
        }
        else{
            echo json_encode(array('code' => 0, 'msg' => 'Done!'));
        }
    }


    function export_student_bills(){
        $this->autoLayout = false;

        //Create seesion
        $this->Session->write('Downloading', 'Excel Date');

        $download = false;

        $_POST = $this->StaffUser->sanitize($_POST);

        $page = isset($_POST['data_page']) ? $_POST['data_page'] : 1;
        /** Sort order */
        $qtype = isset($_POST['data_query_column']) ? $_POST['data_query_column'] : '';
        /** Search column */
        $search_query = isset($_POST['data_query']) ? $_POST['data_query'] : '';
        /** @var $filter */
        $filter_program = isset($_POST['data_program']) ? $_POST['data_program'] : 0;
        /** @var $filter */
        $filter_level = isset($_POST['data_level']) ? $_POST['data_level'] : 0;
        /** @var $filter */
        $academic_year = isset($_POST['data_academic_year']) ? $_POST['data_academic_year'] : 0;
        $rp = isset($_POST['data_limit']) ? $_POST['data_limit'] : 10000;

        $limit = $rp;
        $start = ($page - 1) * $rp;

        //get users id for this company only
        $condition_array = array(
            'StudentUser.deleted' => 'n'
        );

        if ($filter_program != 0) {
            $condition_array['StudentUser.program_id'] = $filter_program;
        }
        if ($filter_level != 0) {
            $condition_array['StudentUser.level'] = $filter_level;
        }

        if (!empty($search_query)) {
            if ($qtype == 'username') {
                $condition_array = array(
                    'StudentUser.index_number' => $search_query,
                    'StudentUser.deleted' => 'n'
                );
            } elseif ($qtype == 'name') {
                $condition_array = array(
                    'OR' => array("StudentUser.fname LIKE" => $search_query . '%', "StudentUser.mname LIKE" => $search_query . '%', "StudentUser.lname LIKE" => $search_query . '%'),
                    'StudentUser.deleted' => 'n'
                );
            } else {
                $condition_array = array(
                    "StudentUser.$qtype LIKE" => $search_query . '%',
                    'StudentUser.deleted' => 'n'
                );
            }
        }

        $contain = array(
            'Program' => array('fields' => array('Program.id', 'Program.progid', 'Program.name', 'Program.short_name')),
        );
        $fields = array('StudentUser.id', 'StudentUser.index_number', 'StudentUser.fname', 'StudentUser.mname', 'StudentUser.lname', 'StudentUser.level', 'StudentUser.program_id');
        $data_table = $this->StudentUser->find('all', array('fields' => $fields, 'conditions' => $condition_array, 'contain' => $contain, 'limit' => $start . ',' . $limit, 'recursive' => 1));

        if (!empty($data_table)) {
            $download = true;
        }

        $t_body_data = array();
        foreach ($data_table as $obj) {
            $rows = array();

            $rt = $this->StudentUser->getRawBills($obj['StudentUser']['index_number'], $academic_year);
            $fullname = ucwords(strtolower($obj['StudentUser']['fname'] . ' ' . $obj['StudentUser']['mname'] . ' ' . $obj['StudentUser']['lname']));

            $rows[] = $obj['StudentUser']['index_number'];
            $rows[] = $fullname;
            $rows[] = $obj['StudentUser']['level'];
            $rows[] = $obj['Program']['short_name'];
            $rows[] = $this->formatNumber($rt['total_bill'], 'number');
            $rows[] = $this->formatNumber($rt['total_paid'], 'number');
            $rows[] = $this->formatNumber($rt['total_owed'], 'number');

            $t_body_data[] = $rows;
        }

        $list_data = $t_body_data;
        $list_headers = array('Index Number', 'Name', 'Level', 'Program', 'Total Bill GHS', 'Total Paid GHS', 'Total Owned GHS');
        $filename = 'Student Bills Payments';
        $res = $this->convertToExcel($list_headers, $list_data, $filename);
        $objPHPExcel = $res['excel_obj'];
        $filename = $res['filename'];

        $controller = $this;
        $this->Session->delete('Downloading');
        $this->set(compact('controller', 'download', 'objPHPExcel', 'download', 'filename'));
    }



    function import_student_bills()
    {
        $mesg = false;
        $this->layout = 'import_layout';
        if ($this->request->is('post')) {
            $upload_file = false;
            $save_raw = $save = $this->request->data;
            if (isset($save_raw['Import']['attach'])) {
                if (!empty($save_raw['Import']['attach']['name'])) {
                    $upload_file = true;
                }
            }
            $upload_success = false;
            if ($upload_file) {
                $upload_info = array(
                    'save_path' => 'files/import_student_bills/',
                    'folder' => 'import_student_bills/',
                    'file_name' => $save_raw['Import']['attach']['name'], //$_FILES['uploadfile']['name']
                    'file_type' => $save_raw['Import']['attach']['type'], //$_FILES['uploadfile']['type'];
                    'file_tmp_name' => $save_raw['Import']['attach']['tmp_name'], //$_FILES['uploadfile']['tmp_name']
                    'file_size' => $save_raw['Import']['attach']['size'], //$_FILES['uploadfile']['size']
                    'file_name_prefix' => time(),
                    'check_file_type' => array()
                );
                $result = $this->uploadFile($upload_info);
                if ($result['status']) {
                    $upload_success = true;
                } else {
                    $upload_success = false;
                }
            }

            if ($upload_success) {
                //debug($result);
                $result2 = $this->do_fee_import($result['file_name'], $result['file_path']);
                $mesg = $result2['mesg'];
            }
        }
        $this->set(compact('mesg'));
    }


    function do_fee_import($filename, $file_path)
    {
        ini_set("memory_limit", "512M");
        set_time_limit('1200');

        $auth_user = $this->Session->read('loginData');
        # folder path structure
        $folder = "files/";
        # setup directory pathname
        $folderAbsPath = WWW_ROOT . $folder;
        //do excel import
        $status = true;
        $sheet_name = 'Page_1';

        $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
        $cacheSettings = array(' memoryCacheSize ' => '8MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        /**  Create a new Reader of the type defined in $inputFileType  **/
        $objReader = PHPExcel_IOFactory::createReader('CSV'); //Excel5 for Excel and CSV For csv
        /**  Advise the Reader of which WorkSheets we want to load  **/
        //$objReader->setLoadSheetsOnly($sheet_name); //To read CSV COMMENT OUT
        /** Advise the Reader that we only want to load cell data, not formatting **/
        //$objReader->setReadDataOnly(true);//To read CSV COMMENT OUT
        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($folderAbsPath . $file_path);
        /**  Advise the Reader of which WorkSheets we want to load  **/

        $worksheetTitle = $objPHPExcel->getActiveSheet()->getTitle();
        $highestRow = $objPHPExcel->getActiveSheet()->getHighestRow(); // e.g. 10
        $highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn(); // e.g 'F'

        $fee_type_id = trim($objPHPExcel->getActiveSheet()->getCell("A1")->getValue()); //Feetype id
        $amount = trim($objPHPExcel->getActiveSheet()->getCell("B1")->getValue()); //Amount
        $academic_year = trim($objPHPExcel->getActiveSheet()->getCell("C1")->getValue()); //Accademic year
        $index_number = trim($objPHPExcel->getActiveSheet()->getCell("D1")->getValue()); //Index Number
        if ($fee_type_id != 'fee_type_id') {
            return array('status' => false, 'mesg' => '1-Invalid template. Please make sure you are using the right billing template for the upload.');
        }
        if ($amount != 'amount') {
            return array('status' => false, 'mesg' => '2-Invalid template. Please make sure you are using the right billing template for the upload.');
        }
        if ($academic_year != 'academic_year') {
            return array('status' => false, 'mesg' => '3-Invalid template. Please make sure you are using the right billing template for the upload.');
        }
        if ($index_number != 'index_number') {
            return array('status' => false, 'mesg' => '4-Invalid template. Please make sure you are using the right billing template for the upload.');
        }

        $save = array();
        $current_academic_year = true;


        for ($row = 2; $row < ($highestRow + 1); ++$row) {
            $fee_type_id = trim($objPHPExcel->getActiveSheet()->getCell("A" . $row)->getValue()); //Feetype id
            $amount = trim($objPHPExcel->getActiveSheet()->getCell("B" . $row)->getValue()); //Amount
            $academic_year = trim($objPHPExcel->getActiveSheet()->getCell("C" . $row)->getValue()); //Accademic year
            $index_number = trim($objPHPExcel->getActiveSheet()->getCell("D" . $row)->getValue()); //Index Number
            if (!empty($fee_type_id) && !empty($amount) && !empty($academic_year) && !empty($index_number)) {
                if ($this->current_academic_year == $academic_year) {
                    $trans_type_to_use = 'fee_payment' ;
                    if($fee_type_id == '24'){
                        $trans_type_to_use = 'service_charge' ;
                    }
                    $save[] = array(
                        'fee_type_id' => $fee_type_id,
                        'amount' => doubleval($amount),
                        'academic_year' => $academic_year,
                        'index_number' => strtoupper($index_number),
                        'created_by' => $auth_user['id'],
                        'trans_type_to_use' => $trans_type_to_use
                    );
                } else {
                    $current_academic_year = false;
                }

            }
        }

        # save the data
        if($current_academic_year){
            if ($this->Fee->saveAll($this->Fee->sanitize($save))) {
                return array('status' => true, 'mesg' => "The file was successfully imported!");
            } else {
                return array('status' => false, 'mesg' => 'The file could not be imported.');
            }
        }
        else{
            return array('status' => false, 'mesg' => 'All bills must be in the same current academic year : '.$this->current_academic_year);
        }
    }


    function fees_report()
    {
        /*$auth_user = $this->Session->read('loginData');
        if($auth_user['user_level'] == 'Academic'){ //For Account and Account Clerk Staff Only
            $this->redirect(array('controller'=>'Staff','action' => 'index'));
        }*/

        //$default_fee_type = 'all';
        $program_id = '';

    }


    /* function _get_payment_history($start_dt,$end_dt,$index_no,$fee_type=null,$student_user_ids=null,$aca_ses){
         $condition_array = array('Transaction.trans_type'=>'fee_payment','Transaction.academic_session_id'=>$aca_ses);
         if($index_no != null){
             $student = $this->StudentUser->find('first',array(
                 'fields'=>array('StudentUser.id'),
                 'conditions'=>array('StudentUser.index_number'=>$index_no),
                 'recursive'=>-1
             ));
             if($student){
                 $condition_array['Transaction.student_user_id']=$student['StudentUser']['id'];
             }
             else{
                 $condition_array['Transaction.student_user_id']=0;
             }
         }
         if($student_user_ids){
             $condition_array['Transaction.student_user_id']=$student_user_ids;
         }
         if(!empty($start_dt) || !empty($end_dt)){
             if(!empty($start_dt) && empty($end_dt)){
                 $condition_array['DATE(Transaction.created) >=']=$start_dt;
                 $condition_array['DATE(Transaction.created) <=']=$start_dt;
             }
             elseif(empty($start_dt) && !empty($end_dt)){
                 $condition_array['DATE(Transaction.created) >=']=date('Y-m-d');//from now
                 $condition_array['DATE(Transaction.created) <=']=$end_dt; //to a given end date
             }
             elseif(!empty($start_dt) && !empty($end_dt)){
                 $condition_array['DATE(Transaction.created) >=']=$start_dt;//from now
                 $condition_array['DATE(Transaction.created) <=']=$end_dt; //to a given end date
             }
         }
         $contain=array(
             'TransactionItem'=>array(
                 'fields'=>array('TransactionItem.id','TransactionItem.credit_amt','TransactionItem.fee_id','TransactionItem.transaction_id'),
                 'Fee'=>array(
                     'fields'=>array('Fee.id','Fee.amount'),
                     'FeeType'=>array('fields'=>array('FeeType.id','FeeType.description','FeeType.fee_code'))
                 )
             ),
             'StudentUser'=>array('fields'=>array('StudentUser.id','StudentUser.index_number','StudentUser.fname','StudentUser.mname','StudentUser.lname'))
         );
         $fields=array('Transaction.id','Transaction.created','Transaction.trans_type');
         $data_table = $this->Transaction->find('all', array(
             'fields'=>$fields,
             'conditions' => $condition_array,
             'contain'=>$contain,
             'order' => array("Transaction.id"=>'asc'),
         ));

         $return = array();
         foreach($data_table as $key => $value){
             foreach($value['TransactionItem'] as $trans){
                 $trans['transaction_date'] = $value['Transaction']['created'];
                 $trans['transaction_id'] = $value['Transaction']['id'];
                 $fee_type_id = $trans['Fee']['fee_type_id'];
                 if($fee_type != null){
                     if($fee_type != 'all'){ //After the query Now apply the fee type condition
                         if($fee_type_id != $fee_type){
                             continue;
                         }
                     }
                 }
                 $stu_id = $value['StudentUser']['id'];
                 $return[$stu_id]['stu_data']=$value['StudentUser'];
                 $return[$stu_id]['transactions'][$fee_type_id][]=$trans;
             }
         }

         $result = array();
         $ref_count = 1;
         foreach($return as $key => $value){
             foreach($value['transactions'] as $trans){
                 $description = '';
                 $fee_code = '';
                 $amount = 0.00;
                 $tl_credit_amount = 0.00;
                 foreach($trans as $trans_inner){
                     $trans_ref =  $trans_inner['transaction_id'];
                     $created =  date("Y-m-d", strtotime($trans_inner['transaction_date']));
                     $description = $trans_inner['Fee']['FeeType']['description'];
                     $fee_code = $trans_inner['Fee']['FeeType']['fee_code'];
                     $amount = doubleval($trans_inner['Fee']['amount']);
                     $tl_credit_amount = $tl_credit_amount + doubleval($trans_inner['credit_amt']);
                 }
                 $result[]=array(
                     'id'=>$trans_ref,
                     'trans_item_id'=>$trans_inner['id'],
                     'date'=>$created,
                     'reference'=>$ref_count,
                     'index_number'=>$value['stu_data']['index_number'],
                     'name'=>ucwords(strtolower($value['stu_data']['fname'].' '.$value['stu_data']['mname'].' '.$value['stu_data']['lname'])),
                     'description'=>ucwords(strtolower($description)),
                     'fee_code'=>$fee_code,
                     'amount'=> $amount,
                     'tl_credit_amount'=>$tl_credit_amount,
                     'balance'=>$amount - $tl_credit_amount
                 );
                 $ref_count = $ref_count + 1;
             }
         }
         return $result;
     }*/


    function _get_payment_history($start_dt, $end_dt, $index_no, $fee_type, $student_user_ids = null, $aca_ses)
    {

        $conditions_str = " WHERE Transaction.trans_type='fee_payment' AND Transaction.academic_session_id='$aca_ses'";
        if ($index_no != null) {
            $student = $this->StudentUser->find('first', array(
                'fields' => array('StudentUser.id'),
                'conditions' => array('StudentUser.index_number' => $index_no),
                'recursive' => -1
            ));
            if ($student) {
                $conditions_str .= " AND Transaction.student_user_id='" . $student['StudentUser']['id'] . "'";
            }
        }
        if ($student_user_ids) {
            $conditions_str .= " AND Transaction.student_user_id IN (" . implode(',', $student_user_ids) . ")";
        }
        if ($fee_type != 'all') {
            $conditions_str .= " AND Fee.fee_type_id='" . $fee_type . "'";
        }

        if (!empty($start_dt) || !empty($end_dt)) {
            if (!empty($start_dt) && empty($end_dt)) {
                $conditions_str .= " AND DATE(Transaction.created) >='" . $start_dt . "' AND DATE(Transaction.created) <='" . $start_dt . "'";
            } elseif (empty($start_dt) && !empty($end_dt)) {
                $conditions_str .= " AND DATE(Transaction.created) >='" . date('Y-m-d') . "' AND DATE(Transaction.created) <='" . $end_dt . "'";
            } elseif (!empty($start_dt) && !empty($end_dt)) {
                $conditions_str .= " AND DATE(Transaction.created) >='" . $start_dt . "' AND DATE(Transaction.created) <='" . $end_dt . "'";
            }
        }

        //debug($condition_array);
        $joins = " LEFT JOIN student_users AS StudentUser ON Transaction.student_user_id=StudentUser.id";
        $joins .= " LEFT JOIN transaction_items AS TransactionItem ON Transaction.id=TransactionItem.transaction_id";
        $joins .= " LEFT JOIN fees AS Fee ON TransactionItem.fee_id=Fee.id";
        $joins .= " LEFT JOIN fee_types AS FeeType ON Fee.fee_type_id=FeeType.id";

        $fields_str = " Transaction.id,Transaction.created,Transaction.trans_type,";
        $fields_str .= " StudentUser.id,StudentUser.index_number,StudentUser.fname,StudentUser.mname,StudentUser.lname,";
        $fields_str .= " TransactionItem.id,TransactionItem.credit_amt,TransactionItem.fee_id,TransactionItem.transaction_id,";
        $fields_str .= " Fee.id,Fee.amount,";
        $fields_str .= " FeeType.id,FeeType.description,FeeType.fee_code";

        $query = "SELECT $fields_str FROM transactions AS Transaction $joins $conditions_str ORDER BY Transaction.id ASC;";
        //debug($query);

        $data_table = $this->Transaction->query($query);
        //debug($data_table);

        $return = $data_table;
        //debug($return);

        $result = array();
        $ref_count = 1;
        foreach ($return as $key => $value) {
            //foreach($value['transactions'] as $trans){
            $amount = doubleval($value['Fee']['amount']);
            $tl_credit_amount = doubleval($value['TransactionItem']['credit_amt']);

            $result[] = array(
                'id' => $value['Transaction']['id'],
                'trans_item_id' => $value['TransactionItem']['id'],
                'date' => $this->convertDate($value['Transaction']['created'], 'mysql'),
                'reference' => $ref_count,
                'index_number' => $value['StudentUser']['index_number'],
                'name' => ucwords(strtolower($value['StudentUser']['fname'] . ' ' . $value['StudentUser']['mname'] . ' ' . $value['StudentUser']['lname'])),
                'description' => ucwords(strtolower($value['FeeType']['description'])),
                'fee_code' => $value['FeeType']['fee_code'],
                'amount' => $amount,
                'tl_credit_amount' => $tl_credit_amount,
                'balance' => $amount - $tl_credit_amount
            );
            $ref_count = $ref_count + 1;
            // }
        }
        //debug($result);

        return $result;
    }


    function _get_payment_history2($start_dt, $end_dt, $fee_type = null, $aca_ses)
    {

        $condition_array = array('Transaction.trans_type' => 'fee_payment', 'Transaction.academic_session_id' => $aca_ses);

        $acc_ses_data = $this->AcademicSession->getAcademicSessionById($aca_ses);
        $acca_year = $this->current_academic_year;
        if (!empty($acc_ses_data)) {
            $acca_year = $acc_ses_data['AcademicSession']['acc_year'];
        }
        $fee_ids = array();
        if ($fee_type != null) {
            $fee_ids_data = $this->Fee->find('all', array(
                'fields' => array('id'),
                'conditions' => array('fee_type_id' => $fee_type, 'academic_year' => $acca_year),
                'recursive' => -1
            ));
            foreach ($fee_ids_data as $d) {
                $fee_ids[] = $d['Fee']['id'];
            }
        }

        if (!empty($start_dt) || !empty($end_dt)) {
            //'Order.order_date >=' => $start_dt, 'Order.order_date <=' => $end_dt
            if (!empty($start_dt) && empty($end_dt)) {
                $condition_array['DATE(Transaction.created) >='] = $start_dt;
                $condition_array['DATE(Transaction.created) <='] = $start_dt;
            } elseif (empty($start_dt) && !empty($end_dt)) {
                $condition_array['DATE(Transaction.created) >='] = date('Y-m-d'); //from now
                $condition_array['DATE(Transaction.created) <='] = $end_dt; //to a given end date
            } elseif (!empty($start_dt) && !empty($end_dt)) {
                $condition_array['DATE(Transaction.created) >='] = $start_dt; //from now
                $condition_array['DATE(Transaction.created) <='] = $end_dt; //to a given end date
                //$condition_array['Transaction.created >=']=$start_dt;//from now
                //$condition_array['Transaction.created <=']=$end_dt; //to a given end date
            }
        }

        $contain = array(
            'TransactionItem' => array(
                'fields' => array('TransactionItem.id', 'TransactionItem.credit_amt', 'TransactionItem.fee_id', 'TransactionItem.transaction_id'),
                'conditions' => array('TransactionItem.fee_id' => $fee_ids),
                'Fee' => array(
                    'fields' => array('Fee.id', 'Fee.amount'),
                    'FeeType' => array('fields' => array('FeeType.id', 'FeeType.description'))
                )
            ),
            'StudentUser' => array('fields' => array('StudentUser.id', 'StudentUser.index_number', 'StudentUser.fname', 'StudentUser.mname', 'StudentUser.lname'))
        );

        $fields = array('Transaction.id', 'Transaction.created', 'Transaction.trans_type');
        $data_table = $this->Transaction->find('all', array(
            'fields' => $fields,
            'conditions' => $condition_array,
            'contain' => $contain,
            'order' => array("Transaction.id" => 'asc'),
            // 'recursive' => 3
        ));

        $return = array();
        foreach ($data_table as $key => $value) {
            foreach ($value['TransactionItem'] as $trans) {
                $trans['transaction_date'] = $value['Transaction']['created'];
                $trans['transaction_id'] = $value['Transaction']['id'];
                $fee_type_id = $trans['Fee']['fee_type_id'];
                /*if($fee_type != null){
                    if($fee_type != 'all'){ //After the query Now apply the fee type condition
                        if($fee_type_id != $fee_type){
                            continue;
                        }
                    }
                }*/
                $stu_id = $value['StudentUser']['id'];
                $return[$stu_id]['stu_data'] = $value['StudentUser'];
                $return[$stu_id]['transactions'][$fee_type_id][] = $trans;
            }
        }

        //debug($return);

        $result = array();
        $ref_count = 1;
        foreach ($return as $key => $value) {
            foreach ($value['transactions'] as $trans) {
                $description = '';
                $amount = 0.00;
                $tl_credit_amount = 0.00;
                foreach ($trans as $trans_inner) {
                    $trans_ref = $trans_inner['transaction_id'];
                    $created = date("Y-m-d", strtotime($trans_inner['transaction_date']));
                    $description = $trans_inner['Fee']['FeeType']['description'];
                    $amount = doubleval($trans_inner['Fee']['amount']);
                    $tl_credit_amount = $tl_credit_amount + doubleval($trans_inner['credit_amt']);
                }
                $result[] = array(
                    'id' => $trans_ref,
                    'date' => $created,
                    'reference' => $ref_count,
                    'index_number' => $value['stu_data']['index_number'],
                    'name' => ucwords(strtolower($value['stu_data']['fname'] . ' ' . $value['stu_data']['mname'] . ' ' . $value['stu_data']['lname'])),
                    'description' => ucwords(strtolower($description)),
                    'amount' => $amount,
                    'tl_credit_amount' => $tl_credit_amount,
                    'balance' => $amount - $tl_credit_amount
                );
                $ref_count = $ref_count + 1;
            }
        }
        //debug($result);

        return $result;
    }

    function payment_history()
    {
        /*$auth_user = $this->Session->read('loginData');
        if($auth_user['user_level'] != 'Academic'){ //For Academic Staff Only
            $this->redirect(array('controller'=>'Staff','action' => 'index'));
        }*/

        $default_fee_type = 'all';
        $fee_type = '';
        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $index_no = '10011571';
        $aca_ses = $this->current_academic_session_id;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $index_no = $p_data['index_no'];
            $fee_type = $default_fee_type = $p_data['fee_type'];
            $aca_ses = $p_data['academic_session'];
        }

        $data_table = $this->_get_payment_history($start_dt, $end_dt, $index_no, $fee_type, null, $aca_ses);
        //debug($data_table);
        //debug($condition_array);
        $fee_types = array('all' => 'All');
        $fee_types_data = $this->FeeType->find('all', array(
            'fields' => array('FeeType.id', 'FeeType.description'),
            'conditions' => array('FeeType.deleted' => 'n'),
            'order' => array('FeeType.description' => 'asc'),
            'recursive' => -1
        ));
        foreach ($fee_types_data as $dt) {
            $fee_types[$dt['FeeType']['id']] = $dt['FeeType']['description'];
        }

        $academic_sessions = $this->getAcademicSessions();

        $controller = $this;

        $this->set(compact('page_title', 'fee_types', 'default_fee_type', 'default_start_dt', 'default_end_dt', 'fee_type', 'data_table', 'index_no', 'controller', 'academic_sessions', 'aca_ses'));
    }


    function print_export_payment_history()
    {
        $download = false;

        $_POST = $this->StaffUser->sanitize($_POST);

        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $fee_type = $_POST['data_fee_type'];
        $index_no = $_POST['data_index_no'];
        $aca_ses = $_POST['data_session'];

        $data_table = $this->_get_payment_history($start_dt, $end_dt, $index_no, $fee_type, null, $aca_ses);

        //$export_title = $omc_name.' Monthly Consolidated '.$product_name;
        //$table_title = $omc_name.' Monthly Consolidated Table '.$product_name;

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_paid = 0;
            $tl_fee_amount = 0;
            $tl_bal = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {

                $rows = array();

                $paid = doubleval($obj['tl_credit_amount']);
                $fee_amount = doubleval($obj['amount']);
                $bal = doubleval($obj['balance']);

                $tl_paid = $tl_paid + $paid;
                $tl_fee_amount = $tl_fee_amount + $fee_amount;
                $tl_bal = $tl_bal + $bal;

                $rows[] = $obj['date'];
                $rows[] = $obj['name'];
                $rows[] = $obj['index_number'];
                $rows[] = $obj['description'];
                $rows[] = $obj['fee_code'];
                $rows[] = $this->formatNumber($obj['amount'], 'number');
                $rows[] = $this->formatNumber($obj['tl_credit_amount'], 'number');
                $rows[] = $this->formatNumber($obj['balance'], 'number');

                $t_body_data[] = $rows;

            }
            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            $rows[] = $this->formatNumber($tl_fee_amount, 'number');
            $rows[] = $this->formatNumber($tl_paid, 'number');
            $rows[] = $this->formatNumber($tl_bal, 'number');
            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Student Name', 'Index Number', 'Fee Type', 'Fee Code', 'Fee Amount GHc', 'Total Paid GHc', 'Balance GHc');
            $filename = 'Student Payments History';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    function report()
    {
        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $program_id = 6; //ACCA-UK
        $section = 'all'; //ACCA-UK
        $aca_ses = $this->current_academic_session_id;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $program_id = $p_data['program_id'];
            $section = $p_data['section'];
            $aca_ses = $p_data['academic_session'];
        }

        //Get students ids with filters
        $conditions = array('StudentUser.program_id' => $program_id);
        if ($section != 'all') {
            $conditions['StudentUser.classification'] = $section;
        }
        $stu_list = $this->StudentUser->find('all', array(
            'fields' => array('StudentUser.id', 'StudentUser.index_number', 'StudentUser.program_id'),
            'conditions' => $conditions,
            'recursive' => -1
        ));
        $student_user_ids = array();
        foreach ($stu_list as $dt) {
            $student_user_ids[] = $dt['StudentUser']['id'];
        }

        $data_table = $this->_get_payment_history($start_dt, $end_dt, null, 'all', $student_user_ids, $aca_ses);
        //debug($data_table);
        //debug($condition_array);
        $program_types = array();
        $program_types_data = $this->Program->find('all', array(
            'fields' => array('Program.id', 'Program.name', 'Program.short_name'),
            'order' => array('Program.short_name' => 'asc'),
            'recursive' => -1
        ));
        foreach ($program_types_data as $dt) {
            $program_types[$dt['Program']['id']] = $dt['Program']['short_name'];
        }
        $section_types = array_merge(array('all' => 'All'), $this->classification);
        $academic_sessions = $this->getAcademicSessions();

        $controller = $this;

        $this->set(compact('page_title', 'program_types', 'default_start_dt', 'default_end_dt', 'data_table', 'controller', 'section_types', 'program_id', 'section', 'academic_sessions', 'aca_ses'));
    }


    function print_export_report()
    {
        $download = false;
        $_POST = $this->StaffUser->sanitize($_POST);
        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $program_id = $_POST['data_program_id'];
        $section = $_POST['data_section'];
        $aca_ses = $_POST['data_session'];

        //Get students ids with filters
        $conditions = array('StudentUser.program_id' => $program_id);
        if ($section != 'all') {
            $conditions['StudentUser.classification'] = $section;
        }
        $stu_list = $this->StudentUser->find('all', array(
            'fields' => array('StudentUser.id', 'StudentUser.index_number', 'StudentUser.program_id'),
            'conditions' => $conditions,
            'recursive' => -1
        ));
        $student_user_ids = array();
        foreach ($stu_list as $dt) {
            $student_user_ids[] = $dt['StudentUser']['id'];
        }

        $data_table = $this->_get_payment_history($start_dt, $end_dt, null, 'all', $student_user_ids, $aca_ses);

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_paid = 0;
            $tl_fee_amount = 0;
            $tl_bal = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {

                $rows = array();

                $paid = doubleval($obj['tl_credit_amount']);
                $fee_amount = doubleval($obj['amount']);
                $bal = doubleval($obj['balance']);

                $tl_paid = $tl_paid + $paid;
                $tl_fee_amount = $tl_fee_amount + $fee_amount;
                $tl_bal = $tl_bal + $bal;

                $rows[] = $obj['date'];
                $rows[] = $obj['reference'];
                $rows[] = $obj['index_number'];
                $rows[] = $obj['name'];
                $rows[] = $obj['description'];
                // $rows[]= $this->formatNumber($obj['amount'],'number');
                $rows[] = $this->formatNumber($obj['tl_credit_amount'], 'number');
                // $rows[]= $this->formatNumber($obj['balance'],'number');

                $t_body_data[] = $rows;

            }
            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            //$rows[]= $this->formatNumber($tl_fee_amount,'number');
            $rows[] = $this->formatNumber($tl_paid, 'number');
            //$rows[]= $this->formatNumber($tl_bal,'number');
            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Reference', 'Student ID', 'Details', 'Fee Type', 'Total Paid GHc');
            $filename = 'Student Payments History By Program and Session';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    function report_fee_type()
    {
        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $fee_type = 1; //
        $aca_ses = $this->current_academic_session_id;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $fee_type = $p_data['fee_type'];
            $aca_ses = $p_data['academic_session'];
        }

        //$data_table = $this->_get_payment_history($start_dt,$end_dt,null,$fee_type,null,$aca_ses);
        $data_table = $this->_get_payment_history($start_dt, $end_dt, null, $fee_type, null, $aca_ses);
        //$data_table = $this->_get_payment_history2($start_dt,$end_dt,$fee_type,$aca_ses);
        //debug($data_table);
        //debug($condition_array);
        $fee_types = array();
        $fee_types_data = $this->FeeType->find('all', array(
            'fields' => array('FeeType.id', 'FeeType.description'),
            'order' => array('FeeType.description' => 'asc'),
            'recursive' => -1
        ));
        foreach ($fee_types_data as $dt) {
            $fee_types[$dt['FeeType']['id']] = $dt['FeeType']['description'];
        }
        //$fee_types= array_merge(array('all'=>'All'),$fee_types);
        $academic_sessions = $this->getAcademicSessions();

        $controller = $this;

        $this->set(compact('page_title', 'fee_types', 'default_start_dt', 'default_end_dt', 'data_table', 'controller', 'fee_type', 'academic_sessions', 'aca_ses'));
    }


    function print_export_report_fee_type()
    {
        $download = false;

        $_POST = $this->StaffUser->sanitize($_POST);
        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $fee_type = $_POST['data_fee_type'];
        $aca_ses = $_POST['data_session'];

        //Get students ids with filters
        /*$conditions = array('StudentUser.program_id'=>$program_id);
        if($section != 'all'){
            $conditions['StudentUser.classification']=$section;
        }
        $stu_list = $this->StudentUser->find('all',array(
            'fields'=>array('StudentUser.id','StudentUser.index_number','StudentUser.program_id'),
            'conditions'=>$conditions,
            'recursive'=>-1
        ));
        $student_user_ids = array();
        foreach($stu_list as $dt){
            $student_user_ids[]=$dt['StudentUser']['id'];
        }*/

        $data_table = $this->_get_payment_history($start_dt, $end_dt, null, $fee_type, null, $aca_ses);

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_paid = 0;
            $tl_fee_amount = 0;
            $tl_bal = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {

                $rows = array();

                $paid = doubleval($obj['tl_credit_amount']);
                $fee_amount = doubleval($obj['amount']);
                $bal = doubleval($obj['balance']);

                $tl_paid = $tl_paid + $paid;
                $tl_fee_amount = $tl_fee_amount + $fee_amount;
                $tl_bal = $tl_bal + $bal;

                $rows[] = $obj['date'];
                $rows[] = $obj['reference'];
                $rows[] = $obj['index_number'];
                $rows[] = $obj['name'];
                $rows[] = $obj['description'];
                // $rows[]= $this->formatNumber($obj['amount'],'number');
                $rows[] = $this->formatNumber($obj['tl_credit_amount'], 'number');
                // $rows[]= $this->formatNumber($obj['balance'],'number');

                $t_body_data[] = $rows;

            }
            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            //$rows[]= $this->formatNumber($tl_fee_amount,'number');
            $rows[] = $this->formatNumber($tl_paid, 'number');
            //$rows[]= $this->formatNumber($tl_bal,'number');
            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Reference', 'Student ID', 'Details', 'Fee Type', 'Total Paid GHc');
            $filename = 'Student Payments History By Fee Type';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    function report_student()
    {
        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $index_number = '10011571'; //
        $aca_ses = $this->current_academic_session_id;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $index_number = $p_data['index_number'];
            $aca_ses = $p_data['academic_session'];
        }

        $data_table = $this->_get_payment_history($start_dt, $end_dt, $index_number, 'all', null, $aca_ses);

        $academic_sessions = $this->getAcademicSessions();

        $controller = $this;

        $this->set(compact('page_title', 'fee_types', 'default_start_dt', 'default_end_dt', 'data_table', 'controller', 'fee_type', 'index_number', 'academic_sessions', 'aca_ses'));
    }


    function print_export_report_student()
    {
        $download = false;

        $_POST = $this->StaffUser->sanitize($_POST);
        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $index_number = $_POST['data_index_number'];
        $aca_ses = $_POST['data_session'];

        //Get students ids with filters
        /*$conditions = array('StudentUser.program_id'=>$program_id);
        if($section != 'all'){
            $conditions['StudentUser.classification']=$section;
        }
        $stu_list = $this->StudentUser->find('all',array(
            'fields'=>array('StudentUser.id','StudentUser.index_number','StudentUser.program_id'),
            'conditions'=>$conditions,
            'recursive'=>-1
        ));
        $student_user_ids = array();
        foreach($stu_list as $dt){
            $student_user_ids[]=$dt['StudentUser']['id'];
        }*/

        $data_table = $this->_get_payment_history($start_dt, $end_dt, $index_number, 'all', null, $aca_ses);

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_paid = 0;
            $tl_fee_amount = 0;
            $tl_bal = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {

                $rows = array();

                $paid = doubleval($obj['tl_credit_amount']);
                $fee_amount = doubleval($obj['amount']);
                $bal = doubleval($obj['balance']);

                $tl_paid = $tl_paid + $paid;
                $tl_fee_amount = $tl_fee_amount + $fee_amount;
                $tl_bal = $tl_bal + $bal;

                $rows[] = $obj['date'];
                $rows[] = $obj['reference'];
                $rows[] = $obj['index_number'];
                $rows[] = $obj['name'];
                $rows[] = $obj['description'];
                // $rows[]= $this->formatNumber($obj['amount'],'number');
                $rows[] = $this->formatNumber($obj['tl_credit_amount'], 'number');
                // $rows[]= $this->formatNumber($obj['balance'],'number');

                $t_body_data[] = $rows;

            }
            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            //$rows[]= $this->formatNumber($tl_fee_amount,'number');
            $rows[] = $this->formatNumber($tl_paid, 'number');
            //$rows[]= $this->formatNumber($tl_bal,'number');
            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Reference', 'Student ID', 'Details', 'Fee Type', 'Total Paid GHc');
            $filename = 'Payments History By Student';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    function service_requests()
    {
        $start_dt = $default_start_dt = date('Y-m-d');
        $end_dt = $default_end_dt = date('Y-m-d');
        $aca_ses = $this->current_academic_session_id;
        $default_fee_type = 'all';
        $fee_type = '';
        $index_no = '';
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $aca_ses = $p_data['academic_session'];
            $index_no = trim($p_data['index_no']);
            $index_no = empty($index_no) ? null:$index_no;
            $fee_type = $default_fee_type = $p_data['fee_type'];
            $fee_type = $fee_type == 'all' ? null:$fee_type;
            // $program_id = $p_data['program_id'];
            // $section = $p_data['section'];
        }

        //Get students ids with filters
        $conditions = array('StudentServiceRequest.academic_session_id' => $aca_ses);
        // $conditions = array();
        $conditions['DATE(StudentServiceRequest.created) >='] = $start_dt;
        $conditions['DATE(StudentServiceRequest.created) <='] = $end_dt;
        if($index_no != null){
            $conditions['StudentUser.index_number ='] = $index_no;
        }
        if($fee_type != null){
            $conditions['Fee.fee_type_id ='] = $fee_type;
        }

        $service_requests_data = $this->StudentServiceRequest->find('all', array(
            'fields' => array('StudentServiceRequest.id', 'StudentServiceRequest.fee_id', 'StudentServiceRequest.served', 'StudentServiceRequest.served_date', 'StudentServiceRequest.created'),
            'conditions' => $conditions,
            'contain' => array(
                'StudentUser' => array('fields' => array('StudentUser.index_number', 'StudentUser.fname', 'StudentUser.mname', 'StudentUser.lname')),
                'Fee' => array('fields' => array('Fee.id', 'Fee.fee_type_id'))
            )
        ));
        $data_table = array();
        foreach ($service_requests_data as $arr) {
            $trans = $this->__getTransactionItemData($arr['StudentUser']['id'], $arr['StudentServiceRequest']['fee_id']);

            if (empty($trans)) {
                continue;
            }

            if (isset($trans['FeeType'])) {
                $FeeType = $trans['FeeType'];
            } else {
                $FeeType = array(
                    'description' => ''
                );
            }
            if (isset($trans['Fee'])) {
                $Fee = $trans['Fee'];
            } else {
                $Fee = array(
                    'amount' => ''
                );
            }
            if (isset($trans['TransactionItem'])) {
                $TransactionItem = $trans['TransactionItem'];
            } else {
                $TransactionItem = array(
                    'credit_amt' => ''
                );
            }

            if ($arr['StudentServiceRequest']['served'] == 'Yes') {
                $served_date = $arr['StudentServiceRequest']['served_date'];
            } else {
                $served_date = '';
            }

            $data_table[] = array(
                'id' => $arr['StudentServiceRequest']['id'],
                'index_number' => $arr['StudentUser']['index_number'],
                'name' => $arr['StudentUser']['fname'] . ' ' . $arr['StudentUser']['mname'] . ' ' . $arr['StudentUser']['lname'],
                'fee' => $FeeType['description'],
                'fee_amount' => $Fee['amount'],
                'paid' => $TransactionItem['credit_amt'],
                'balance' => $Fee['amount'] - $TransactionItem['credit_amt'],
                'served' => $arr['StudentServiceRequest']['served'],
                'request_date' => $arr['StudentServiceRequest']['created'],
                'served_date' => $served_date
            );
        }

        $academic_sessions = $this->getAcademicSessions();

        $fee_types = array('all' => 'All');
        $fee_types_data = $this->FeeType->find('all', array(
            'fields' => array('FeeType.id', 'FeeType.description'),
            'conditions' => array('FeeType.fee_category'=>'Conditional Fees','FeeType.deleted' => 'n'),
            'order' => array('FeeType.description' => 'asc'),
            'recursive' => -1
        ));
        foreach ($fee_types_data as $dt) {
            $fee_types[$dt['FeeType']['id']] = $dt['FeeType']['description'];
        }

        $controller = $this;

        $this->set(compact('page_title', 'program_types', 'default_start_dt', 'default_end_dt', 'data_table', 'controller', 'section_types', 'program_id', 'section', 'academic_sessions', 'aca_ses','fee_types','default_fee_type','index_no'));
    }


    function __getTransactionItemData($student_id, $fee_id)
    {
        $conditions = array('Transaction.student_user_id' => $student_id, 'TransactionItem.fee_id' => $fee_id);
        $service_requests_data = $this->TransactionItem->find('first', array(
            'fields' => array('TransactionItem.transaction_id', 'TransactionItem.credit_amt'),
            'conditions' => $conditions,
            'contain' => array(
                'Transaction' => array('fields' => array('Transaction.created')),
                'Fee' => array(
                    'fields' => array('Fee.id', 'Fee.fee_type_id', 'Fee.amount'),
                    'FeeType' => array(
                        'fields' => array('FeeType.id', 'FeeType.description')
                    )
                )
            )
        ));

        if (!empty($service_requests_data)) {
            $conditions = array('Fee.id' => $fee_id);
            $fee_data = $this->Fee->find('first', array(
                'fields' => array('Fee.id', 'Fee.amount'),
                'conditions' => $conditions,
                'contain' => array(
                    'FeeType' => array('fields' => array('FeeType.description')),
                )
            ));
            if ($fee_data) {
                $service_requests_data['Fee'] = $fee_data['Fee'];
                $service_requests_data['FeeType'] = $fee_data['FeeType'];
            } else {
                $service_requests_data = array();
            }
        }
        return $service_requests_data;
    }


    function  update_service_request()
    {
        $this->autoRender = false;
        $this->autoLayout = false;
        $auth_user = $this->Session->read('loginData');
        if ($this->request->is('ajax')) {
            $_POST = $this->StaffUser->sanitize($_POST);
            $data = array('StudentServiceRequest' => array(
                'id' => $_POST['id'],
                'served' => 'Yes',
                'modified_by' => $auth_user['id'],
                'served_date' => date('Y-m-d H:i:s')

            ));
            $gdata = $this->StudentServiceRequest->save($data['StudentServiceRequest']);
            if ($gdata) {
                if ($_POST['id'] > 0) {
                    return json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                } else {
                    return json_encode(array('code' => 0, 'msg' => 'Data Saved', 'id' => $this->StudentServiceRequest->id));
                }
            } else {
                return json_encode(array('code' => 1, 'msg' => __('Data cannot be saved')));
            }
        }
    }


    function student_users($type = 'get')
    {

        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] != 'Academic Registration Officer') {
            $this->redirect(array('controller' => 'Staff', 'action' => 'index'));
        }


        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'StudentUser.deleted' => 'n',
                    );

                    if (!empty($search_query)) {
                        if ($qtype == 'index_number') {
                            $condition_array = array(
                                'StudentUser.index_number' => $search_query,
                                'StudentUser.deleted' => 'n',
                            );
                        }
                        if ($qtype == 'reg_no') {
                            $condition_array = array(
                                'StudentUser.reg_no' => $search_query,
                                'StudentUser.deleted' => 'n',
                            );
                        } else {
                            $condition_array = array(
                                "StudentUser.$qtype LIKE" => $search_query . '%',
                                'StudentUser.deleted' => 'n',
                            );
                        }
                    }

                    $contain = array(
                        'Program' => array('fields' => array('Program.id', 'Program.short_name', 'Program.progid'))
                    );

                    $data_table = $this->StudentUser->find('all', array('conditions' => $condition_array, 'contain' => $contain, 'order' => "StudentUser.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->StudentUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $active = $obj['StudentUser']['active'];
                            $active_lable = 'Enabled';
                            if ($active == 'n') {
                                $active_lable = 'Disabled';
                            }
                            $return_arr[] = array(
                                'id' => $obj['StudentUser']['id'],
                                'cell' => array(
                                    $active_lable,
                                    $obj['StudentUser']['reg_no'],
                                    $obj['StudentUser']['index_number'],
                                    ucwords(strtolower($obj['StudentUser']['fname'])),
                                    ucwords(strtolower($obj['StudentUser']['mname'])),
                                    ucwords(strtolower($obj['StudentUser']['lname'])),
                                    $obj['StudentUser']['gender'],
                                    $obj['StudentUser']['date_of_birth'],
                                    $obj['StudentUser']['date_of_admission'],
                                    $obj['StudentUser']['date_of_completion'],
                                    $obj['StudentUser']['nationality'],
                                    $obj['StudentUser']['homephone'],
                                    $obj['StudentUser']['cellphone'],
                                    $obj['StudentUser']['email'],
                                    $obj['Program']['progid'],
                                    $obj['StudentUser']['level'],
                                    $obj['StudentUser']['entry_level'],
                                    $obj['StudentUser']['applicant_id'],
                                    $obj['StudentUser']['classification'],
                                    $obj['StudentUser']['graduate'] == 1 ? 'Yes' : 'No',
                                    $obj['StudentUser']['completed'] == 1 ? 'Yes' : 'No'
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    if ($_POST['id'] == 0) {
                        //check if username does not exist for in this company
                        $username = $_POST['reg_no'];
                        $res = $this->_validateUsername($username, 'student');
                        if ($res) {
                            return json_encode(array('success' => 1, 'msg' => 'Username already exist.'));
                        }
                    }
                    $data = array('StudentUser' => $_POST);
                    //$pass = '';
                    if ($_POST['id'] == 0) {
                        $pass = trim($_POST['date_of_birth']);
                        $data['StudentUser']['password'] = md5($pass);
                        $data['StudentUser']['username'] = $username;
                        $data['StaffUser']['created_by'] = $auth_user['id'];
                        $pro = $this->Program->find('first', array(
                            'fields' => array('id', 'progid'),
                            'conditions' => array('id' => $_POST['program_id']),
                            'recursive' => -1
                        ));
                        $data['StaffUser']['progid'] = $pro['Program']['progid'];
                    } else {
                        $data['StudentUser']['modified_by'] = $auth_user['id'];
                    }

                    if ($this->StudentUser->save($data['StudentUser'])) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                        } else { //If new pass back extra data if needed.
                            //return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved!  The default password is the date of birth', 'id' => $this->StudentUser->id));
                        }
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Some errors occurred.'));
                    }
                    break;

                case 'reset_password':
                    $data = array(
                        'id' => $_POST['id'],
                        'password' => md5(trim($_POST['password']))
                    );
                    if ($this->StudentUser->save($data)) {
                        return json_encode(array('code' => 0, 'msg' => 'Password has been reset.'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Password could not be reset.'));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('StudentUser');
                    $result = $modObj->updateAll(
                        array('StudentUser.deleted' => "'y'"),
                        array('StudentUser.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;

                case 'profile_config':
                    $data = $_POST['profile_config_opt'];
                    $modObj = ClassRegistry::init('Config');
                    $result = $modObj->save(array(
                        'id' => 1,
                        'students_can_edit_other_profile' => $data
                    ));

                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be Updated'));
                    }
                    break;
            }
        }

        $pro = $this->Program->find('all', array(
            'fields' => array('id', 'progid', 'short_name'),
            //'conditions'=>array('deleted'=>'n'),
            'recursive' => -1
        ));
        $programs = array();
        foreach ($pro as $p) {
            $programs[] = array(
                'id' => $p['Program']['id'],
                'name' => $p['Program']['progid']
            );
        }
        $classify = array();
        foreach ($this->classification as $c) {
            $classify[] = array(
                'id' => $c,
                'name' => $c
            );
        }

        $levels = array();
        foreach ($this->levels as $l) {
            $levels[] = array(
                'id' => $l,
                'name' => $l
            );
        }

        $countries = array();
        foreach ($this->countries as $l) {
            $countries[] = array(
                'id' => $l,
                'name' => $l
            );
        }

        $config = $this->config;
        $this->set(compact('programs', 'classify', 'levels', 'config', 'countries'));
    }


    /* School Fee Types list */
    function fee_types($type = 'get')
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] != 'Academic Registration Officer') {
            $this->redirect(array('controller' => 'Staff', 'action' => 'index'));
        }

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->StaffUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'FeeType.deleted' => 'n',
                    );

                    if (!empty($search_query)) {
                        $condition_array = array(
                            "FeeType.$qtype LIKE" => $search_query . '%',
                            'FeeType.deleted' => 'n',
                        );
                    }
                    $contain = array(
                        'BankBranch' => array('fields' => array('BankBranch.id', 'BankBranch.branch_name_full'))
                    );
                    $data_table = $this->FeeType->find('all', array('conditions' => $condition_array, 'contain' => $contain, 'order' => "FeeType.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->FeeType->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['FeeType']['id'],
                                'cell' => array(
                                    $obj['FeeType']['id'],
                                    $obj['FeeType']['fee_category'],
                                    ucwords(strtolower($obj['FeeType']['description'])),
                                    $obj['FeeType']['fee_code'],
                                    $obj['FeeType']['account_no'],
                                    $obj['BankBranch']['branch_name_full'],
                                    $obj['FeeType']['initial_charge'],
                                    $obj['FeeType']['additional_charge'],
                                    $obj['FeeType']['quantity_limit']
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    $data = array('FeeType' => $_POST);
                    if ($_POST['id'] == 0) {
                        $data['FeeType']['created_by'] = $auth_user['id'];
                    } else {
                        $data['FeeType']['modified_by'] = $auth_user['id'];
                    }

                    $gdata = $this->FeeType->save($data);
                    if ($gdata) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Updated!'));
                        } else {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved', 'id' => $this->FeeType->id));
                        }
                    } else {
                        return json_encode(array('code' => 1, 'msg' => __('Data cannot be saved')));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('FeeType');
                    $result = $modObj->updateAll(
                        array('FeeType.deleted' => "'y'", 'FeeType.modified_by' => $auth_user['id']),
                        array('FeeType.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;
            }
        }

        $bank_branch_data = $this->BankBranch->find('all', array(
            'fields' => array('id', 'branch_name_full'),
            'recursive' => -1
        ));
        $branches = array();
        foreach ($bank_branch_data as $dt) {
            $branches[] = array(
                'id' => $dt['BankBranch']['id'],
                'name' => $dt['BankBranch']['branch_name_full'],
            );
        }

        $quantity_limit_data = array('1' => '1', '2' => '2', '3' => '3', '4' => '4', '5' => '5', 'Any' => 'Any');
        $quantity_limits = array();
        foreach ($quantity_limit_data as $k => $dt) {
            $quantity_limits[] = array(
                'id' => $k,
                'name' => $dt,
            );
        }

        $this->set(compact('branches', 'quantity_limits'));
    }


    function report_reversal()
    {
        /*$auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }*/

        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $branch_id = null;
        $teller = null;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            /*$branch_id = $p_data['branch'];
            $teller = $p_data['teller'];*/
        }

        $type = "'credit_reversal'";

        $data_table = $this->getTransactions($start_dt, $end_dt,$type, $branch_id, $teller);

        $controller = $this;

        $this->set(compact('page_title', 'default_start_dt', 'default_end_dt', 'data_table', 'controller'));
    }


    function print_export_revert_transactions()
    {
        /*$auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }*/

        $download = false;

        $_POST = $this->StaffUser->sanitize($_POST);

        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $branch_id = null;
        $teller = null;
        $type = "'credit_reversal'";
        $data_table = $this->getTransactions($start_dt, $end_dt,$type, $branch_id, $teller);

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_credit = 0;
            $tl_debit = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {
                $trans = $obj['Transaction'];
                $reason = $obj['Transaction']['revert_reason'];
                $stu = $obj['StudentUser'];
                $teller_d = $obj['BankUser'];
                $branch_d = $obj['BankBranch'];
                $trans_details = $obj['TransactionBankDetail'];
                $trans_item = $obj['TransactionItem'];

                $rows = array();

                $credit = doubleval($trans_item['credit_amt']);
                $debit = doubleval($trans_item['debit_amt']);

                $tl_credit = $tl_credit + $credit;
                $tl_debit = $tl_debit + $debit;

                $rows[] = $trans['created'];
                $rows[] = $stu['index_number'];
                $rows[] = $stu['fname'] . ' ' . $stu['mname'] . ' ' . $stu['lname'];
                $rows[] = $this->formatNumber($credit, 'number');
                $rows[] = $stu['account_balance'];
                $rows[]= $reason;
                $t_body_data[] = $rows;

            }

            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            $rows[] = $this->formatNumber($tl_credit, 'number');
            $rows[] = '';
            $rows[] = '';

            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Index Number', 'Student', 'Debits GHc', 'Wallet Balance GHc','Reason');
            $filename = 'Revert Transactions Trails';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    private function getTransactions($start_dt, $end_dt, $type,$branch = null, $teller = null)
    {
        $conditions_str = " WHERE Transaction.trans_type IN ($type)";
        if ($branch != null) {
            $conditions_str .= " AND BankUser.bank_branch_id='" . $branch . "'";
        }
        if ($teller != null) {
            $conditions_str .= " AND Transaction.bank_user_id='" . $teller . "'";
        }

        if (!empty($start_dt) || !empty($end_dt)) {
            if (!empty($start_dt) && empty($end_dt)) {
                $conditions_str .= " AND DATE(Transaction.created) >='" . $start_dt . "' AND DATE(Transaction.created) <='" . $start_dt . "'";
            } elseif (empty($start_dt) && !empty($end_dt)) {
                $conditions_str .= " AND DATE(Transaction.created) >='" . date('Y-m-d') . "' AND DATE(Transaction.created) <='" . $end_dt . "'";
            } elseif (!empty($start_dt) && !empty($end_dt)) {
                $conditions_str .= " AND DATE(Transaction.created) >='" . $start_dt . "' AND DATE(Transaction.created) <='" . $end_dt . "'";
            }
        }

        $joins = " LEFT JOIN transaction_bank_details AS TransactionBankDetail ON Transaction.id=TransactionBankDetail.transaction_id";
        $joins .= " LEFT JOIN transaction_items AS TransactionItem ON Transaction.id=TransactionItem.transaction_id";
        $joins .= " LEFT JOIN bank_users AS BankUser ON Transaction.bank_user_id=BankUser.id";
        $joins .= " LEFT JOIN bank_branches AS BankBranch ON BankUser.bank_branch_id=BankBranch.id";
        $joins .= " LEFT JOIN student_users AS StudentUser ON Transaction.student_user_id=StudentUser.id";

        $fields_str = " Transaction.id,Transaction.created,Transaction.trans_type,Transaction.bank_user_id,Transaction.trans_ref_no,Transaction.reverted,Transaction.revert_reason,Transaction.revert_dt,Transaction.reverted_by,";
        $fields_str .= " TransactionItem.id,TransactionItem.desc,TransactionItem.credit_amt,TransactionItem.debit_amt,TransactionItem.transaction_id,";
        $fields_str .= " TransactionBankDetail.id,TransactionBankDetail.payee_name,TransactionBankDetail.payee_phone,TransactionBankDetail.payee_address,TransactionBankDetail.payment_method,";
        $fields_str .= " BankUser.id,BankUser.username,BankUser.fname,BankUser.mname,BankUser.lname,";
        $fields_str .= " BankBranch.id,BankBranch.branch_name,BankBranch.branch_name_full,";
        $fields_str .= " StudentUser.id,StudentUser.index_number,StudentUser.fname,StudentUser.mname,StudentUser.lname,StudentUser.account_balance";

        $query = "SELECT $fields_str FROM transactions AS Transaction $joins $conditions_str ORDER BY Transaction.id DESC;";
        //debug($query);

        $data_table = $this->Transaction->query($query);

        // debug($data_table);
        //exit;
        return $data_table;
    }


    function invoice(){
        $auth_user = $this->Session->read('loginData');

        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $report_type = 'details';
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->StaffUser->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $report_type = $p_data['report_type'];
        }
        $data_table = $this->ServiceCharge->getCisBills($start_dt,$end_dt,$report_type);

        $report_type_opt = array('details' => 'Details','consolidated' => 'Consolidated');
        $controller = $this;

        $this->set(compact('page_title', 'report_type_opt', 'report_type','default_start_dt', 'default_end_dt', 'data_table','controller'));

    }

}